package com.kyg.tjtoolmanager.db;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.kyg.tjtoolmanager.App;
import com.kyg.tjtoolmanager.module.GlobalVariable;
import com.kyg.tjtoolmanager.module.Log;
import com.kyg.tjtoolmanager.module.Reader;

import java.lang.reflect.Field;
import java.net.MalformedURLException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SQLiteUtils {

    private static final String DB_FILE = "database.db"; // SQLite数据库文件名

    static Connection conn = null;

    public static void initDBTable() throws SQLException, ClassNotFoundException, MalformedURLException {
        conn = getConnection();
        //查询
        ResultSet result = queryAll(conn, GlobalVariable.class.getSimpleName());
        if (result != null && result.next()) {
            printRowData(result, new GlobalVariable());
            App.globalVal = parseRowData(result, new GlobalVariable());
        }
        if (App.globalVal == null) {
            App.globalVal = new GlobalVariable();
            //创建表
            Field[] fields = App.globalVal.getClass().getDeclaredFields();
            String[] columns = new String[fields.length];
            JSONObject jsonObject = (JSONObject) JSON.toJSON(App.globalVal);
            String[] values = new String[fields.length];
            for (int i = 0; i < fields.length; i++) {
                columns[i] = fields[i].getName();
                values[i] = jsonObject.getString(fields[i].getName());
            }
            createTable(conn, GlobalVariable.class.getSimpleName(), columns);
            //插入一条默认数据
            insertRecord(conn, GlobalVariable.class.getSimpleName(), columns, values);
        }

        result = queryAll(conn, Reader.class.getSimpleName());
        if (result == null) {
            Reader reader = new Reader();
            Field[] fields = reader.getClass().getDeclaredFields();
            String[] columns = new String[fields.length];
            for (int i = 0; i < fields.length; i++) {
                columns[i] = fields[i].getName();
            }
            createTable(conn, Reader.class.getSimpleName(), columns);
        } else {
//            SQLiteUtils.insertLog("存在Reader表");
        }

        result = queryAll(conn, Log.class.getSimpleName());
        if (result == null) {
            Log log = new Log();
            Field[] fields = log.getClass().getDeclaredFields();
            String[] columns = new String[fields.length];
            for (int i = 0; i < fields.length; i++) {
                columns[i] = fields[i].getName();
            }
            createTable(conn, Log.class.getSimpleName(), columns);
        } else {
//            SQLiteUtils.insertLog("存在Log表");
        }
    }

    public static void updateHttpUrl() {
        String[] conditionSet = new String[]{"httpHost", "faceIp"};
        Object[] conditionSetValue = new Object[]{App.globalVal.httpHost, App.globalVal.faceIp};
        String[] conditions = new String[]{"id"};
        Object[] conditionsValue = new Object[]{"1"};
        updateRecord(conn, GlobalVariable.class.getSimpleName(), conditionSet, conditionSetValue, conditions, conditionsValue);
    }

    /*start 读写器相关数据库操作*/
    public static List<Reader> getLocalAllReader() {
        List<Reader> readers = new ArrayList<>();
        try {
            //查询所有记录
            ResultSet result = queryAll(conn, Reader.class.getSimpleName());
            while (result.next()) {
                readers.add(parseRowData(result, new Reader()));
            }
        } catch (Exception ex) {
            SQLiteUtils.insertLog("getLocalAllReader exception:" + ex.getMessage());
        }
        return readers;
    }

    public static List<Reader> getReaderByType(String type) {
        List<Reader> readers = new ArrayList<>();
        String[] conditions = new String[]{"type"};
        Object[] conditionsValue = new Object[]{type};
        try {
            ResultSet result = queryByCondition(conn, Reader.class.getSimpleName(), conditions, conditionsValue);
            while (result.next()) {
                readers.add(parseRowData(result, new Reader()));
            }
        } catch (Exception ex) {

        }
        return readers;
    }

    public static boolean insertReader(Reader reader) {
        Field[] fields = reader.getClass().getDeclaredFields();
        String[] columns = new String[fields.length];
        JSONObject jsonObject = (JSONObject) JSON.toJSON(reader);
        String[] values = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            columns[i] = fields[i].getName();
            values[i] = jsonObject.getString(fields[i].getName());
        }
        //插入一条数据
        boolean result = insertRecord(conn, Reader.class.getSimpleName(), columns, values);
        if (result == true) {
            SQLObserver.dispatchChange();
        }
        return result;
    }

    public static boolean updateReader(Reader reader) {
        Field[] fields = reader.getClass().getDeclaredFields();
        String[] conditionSet = new String[fields.length];
        JSONObject jsonObject = (JSONObject) JSON.toJSON(reader);
        String[] conditionSetValue = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            conditionSet[i] = fields[i].getName();
            conditionSetValue[i] = jsonObject.getString(fields[i].getName());
        }
        String[] conditions = new String[]{"id"};
        Object[] conditionsValue = new Object[]{reader.id};
        boolean result = updateRecord(conn, Reader.class.getSimpleName(), conditionSet, conditionSetValue, conditions, conditionsValue);
        if (result)
            SQLObserver.dispatchChange();
        return result;
    }

    public static void deleteReader(Reader reader) {
        deleteRecordById(conn, Reader.class.getSimpleName(), reader.id);
        SQLObserver.dispatchChange();
    }

    public static boolean insertLog(String msg) {
        Log log = new Log(msg);
        System.out.println(log.toString());
        Field[] fields = log.getClass().getDeclaredFields();
        String[] columns = new String[fields.length];
        JSONObject jsonObject = (JSONObject) JSON.toJSON(log);
        String[] values = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            columns[i] = fields[i].getName();
            values[i] = jsonObject.getString(fields[i].getName());
        }
        //插入一条数据
        boolean result = insertRecord(conn, Log.class.getSimpleName(), columns, values);
        return result;
    }

    public static List<Log> getLogs() {
        List<Log> logList = new ArrayList<>();
        try {
            ResultSet result = queryAllLogs(conn, Log.class.getSimpleName());
            while (result.next()) {
                logList.add(parseRowData(result, new Log()));
            }
        } catch (Exception ex) {

        }
        return logList;
    }

    public static void cleanLog() {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(Log.class.getSimpleName());
        try {
            System.out.println("根据条件删除数据: " + sql.toString());
            PreparedStatement pstmt = conn.prepareStatement(sql.toString());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /*end*/

    private static void printRowData(ResultSet result, GlobalVariable table) throws SQLException {
        Field[] fields = table.getClass().getDeclaredFields();
        StringBuilder sb = new StringBuilder("printRowData " + table.getClass().getSimpleName() + " ");
        for (Field field : fields) {
            sb.append(field.getName()).append(":").append(result.getString(field.getName()));
            sb.append(",");
        }
        System.out.println(sb.toString());
    }

    private static GlobalVariable parseRowData(ResultSet result, GlobalVariable table) throws SQLException {
        JSONObject jsonObject = new JSONObject();
        Field[] fields = table.getClass().getDeclaredFields();
        for (Field field : fields) {
            jsonObject.put(field.getName(), result.getString(field.getName()));
        }
        return JSON.parseObject(jsonObject.toJSONString(), GlobalVariable.class);
    }

    private static Reader parseRowData(ResultSet result, Reader table) throws SQLException {
        JSONObject jsonObject = new JSONObject();
        Field[] fields = table.getClass().getDeclaredFields();
        for (Field field : fields) {
            jsonObject.put(field.getName(), result.getString(field.getName()));
        }
        return JSON.parseObject(jsonObject.toJSONString(), Reader.class);
    }

    private static Log parseRowData(ResultSet result, Log table) throws SQLException {
        JSONObject jsonObject = new JSONObject();
        Field[] fields = table.getClass().getDeclaredFields();
        for (Field field : fields) {
            jsonObject.put(field.getName(), result.getString(field.getName()));
        }
        return JSON.parseObject(jsonObject.toJSONString(), Log.class);
    }

    /**
     * 创建SQLite数据库连接
     *
     * @return 数据库连接
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException, MalformedURLException {
        Class.forName("org.sqlite.JDBC");
//        URL url = new URL(DB_FILE);
//        SQLiteUtils.insertLog("db path:"+url.getPath());
        Connection conn = DriverManager.getConnection("jdbc:sqlite:" + DB_FILE);
        return conn;
    }

    /**
     * 关闭数据库连接
     */
    public static void closeConnection() {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建SQLite表
     *
     * @param conn      数据库连接
     * @param tableName 表名
     * @param columns   列名和数据类型的数组
     */
    public static void createTable(Connection conn, String tableName, String[] columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
        for (int i = 0; i < columns.length; i++) {
            sql.append(columns[i]).append(" ").append(getColumnType(columns[i]));
            if (i != columns.length - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        try (Statement stmt = conn.createStatement()) {
            System.out.println("建表sql: " + sql.toString());
            stmt.executeUpdate(sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取列的数据类型
     *
     * @param columnName 列名
     * @return 数据类型
     */
    private static String getColumnType(String columnName) {
/*if (columnName.equalsIgnoreCase("id")) {
return "INTEGER PRIMARY KEY";
} else if (columnName.equalsIgnoreCase("name")) {
return "TEXT";
} else if (columnName.equalsIgnoreCase("age")) {
return "INTEGER";
} else if (columnName.equalsIgnoreCase("gender")) {
return "TEXT";
} else {
return "TEXT";
}*/
        if (columnName.equalsIgnoreCase("id")) {
            return "INTEGER PRIMARY KEY";
        } else {
            return "TEXT";
        }
    }

    /**
     * 插入一条记录
     *
     * @param conn      数据库连接
     * @param tableName 表名
     * @param columns   字段的数组
     * @param values    值的数组
     */
    private static boolean insertRecord(Connection conn, String tableName, Object[] columns, Object[] values) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ").append(tableName).append(" (");
        for (int i = 0; i < columns.length; i++) {
            sql.append(columns[i].toString()).append(",");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(") VALUES (");
        for (int i = 0; i < columns.length; i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        try (PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < values.length; i++) {
                pstmt.setObject(i + 1, values[i]);
            }
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 查询所有记录
     *
     * @param conn      数据库连接
     * @param tableName 表名
     * @return 查询结果
     */
    private static ResultSet queryAll(Connection conn, String tableName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ").append(tableName);
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private static ResultSet queryAllLogs(Connection conn, String tableName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ").append(tableName).append(" order by time desc");
        System.out.println("查询所有记录: " + sql.toString());
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询指定字段的记录
     *
     * @param conn       数据库连接
     * @param tableName  表名
     * @param columnName 列名
     * @return 查询结果
     */
    private static ResultSet queryByColumn(Connection conn, String tableName, String columnName) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ").append(columnName).append(" FROM ").append(tableName);
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 查询指定条件的记录
     *
     * @param conn       数据库连接
     * @param tableName  表名
     * @param conditions 条件字段的数组
     * @param values     条件值的数组
     * @return 查询结果
     */
    private static ResultSet queryByCondition(Connection conn, String tableName, String[] conditions, Object[] values) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ").append(tableName).append(" WHERE ");
        for (int i = 0; i < conditions.length; i++) {
            sql.append(conditions[i]).append("=?");
            if (i != conditions.length - 1) {
                sql.append(" AND ");
            }
        }
        System.out.println("询指定条件的记录: " + sql.toString());
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < values.length; i++) {
                pstmt.setObject(i + 1, values[i]);
            }
            return pstmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 更新一条记录
     *
     * @param conn              数据库连接
     * @param tableName         表名
     * @param conditionSet      更新字段的数组
     * @param conditionSetValue 更新字段的数组
     * @param conditions        条件的数组
     * @param conditionsValue   条件值的数组
     */
    private static boolean updateRecord(Connection conn, String tableName, String[] conditionSet, Object[] conditionSetValue, String[] conditions, Object[] conditionsValue) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(tableName).append(" SET ");
        for (int i = 0; i < conditionSet.length; i++) {
            sql.append(conditionSet[i]).append("=?");
            if (i != conditionSet.length - 1) {
                sql.append(",");
            }
        }
        sql.append(" WHERE ");
        for (int i = 0; i < conditions.length; i++) {
            sql.append(conditions[i]).append("=?");
            if (i != conditions.length - 1) {
                sql.append(" AND ");
            }
        }

        try {
            System.out.println("更新一条记录: " + sql.toString());
            PreparedStatement pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < conditionSetValue.length; i++) {
                pstmt.setObject(i + 1, conditionSetValue[i]);
            }
            for (int i = 0; i < conditionsValue.length; i++) {
                pstmt.setObject(i + conditionSet.length + 1, conditionsValue[i]);
            }
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 删除一条记录
     *
     * @param conn            数据库连接
     * @param tableName       表名
     * @param conditions      条件的数组
     * @param conditionsValue 条件值的数组
     */
    private static void deleteRecord(Connection conn, String tableName, String[] conditions, Object[] conditionsValue) {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(tableName).append(" WHERE ");
        for (int i = 0; i < conditions.length; i++) {
            sql.append(conditions[i]).append("=?");
            if (i != conditions.length - 1) {
                sql.append(" AND ");
            }
        }

        try {
            System.out.println("根据条件删除数据: " + sql.toString());
            PreparedStatement pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < conditionsValue.length; i++) {
                pstmt.setObject(i + 1, conditionsValue[i]);
            }
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据ID删除一条记录
     *
     * @param conn      数据库连接
     * @param tableName 表名
     * @param id        ID值
     */
    private static void deleteRecordById(Connection conn, String tableName, String id) {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(tableName).append(" WHERE id=?");
        try {
            System.out.println("根据条件删除数据: " + sql.toString());
            PreparedStatement pstmt = conn.prepareStatement(sql.toString());
            pstmt.setString(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
